﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using Dapper;
using DapperExtensions;
using ExaminationSys.DataAccess.Entity;
using SQLinq;
using SQLinq.Dapper;

namespace ExaminationSys.DataAccess.Repository
{
    public class DBSubjectQuestionRepository : Repository<DBSubjectQuestion>
    {
        public DBSubjectQuestionRepository(SqlConnection context)
            : base(context)
        {
            db = context;
        }
        private SqlConnection db;
        public override DBSubjectQuestion GetById(dynamic id)
        {
            return base.ObjectSet.Get<DBSubjectQuestion>(db, id, null, null);
        }
    }

    public class DBSubjectQuestionService
    {
        private static readonly DBQuestionService ProxyQuestion = ObjectFactory<DBQuestionService>.Create();
        private DBSubjectQuestionService()
        {
        }

        public DBSubjectQuestion Get(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.DBSubjectQuestion.GetById(id);
                return result;

            }
        }

        public bool ChecKSubjectQuestion(Guid subjectid, Guid questionid)
        {
            using (var db = DataContent.DBServer())
            {
                var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pg.Predicates.Add(Predicates.Field<DBSubjectQuestion>(f => f.DBSubjectID, Operator.Eq, subjectid));
                pg.Predicates.Add(Predicates.Field<DBSubjectQuestion>(f => f.DBQuestionID, Operator.Eq, questionid));
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.DBSubjectQuestion.GetList(pg).FirstOrDefault();
                return result != null;
            }
        }

        public List<DBSubjectQuestion> GetAll()
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.DBSubjectQuestion.GetList();
                return result.ToList();
            }
        }

        public bool Delete(List<string> ids)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"delete from DBSubjectQuestion where DBQuestionID=@TmpId",
                                         new[] { new { TmpId = id } });
                    if (retCode <= 0)
                    {
                        break;
                    }
                }
                flag = true;
            }
            return flag;
        }

        public bool Delete(Guid subjectid, Guid dbquestionid)
        {
            if (subjectid == null || subjectid == null) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                retCode = db.Execute(@"delete from DBSubjectQuestion where DBSubjectID=@sid and DBQuestionID=@did",
                                     new[] { new { sid = subjectid, did = dbquestionid } });
                flag = retCode > 0;
            }
            return flag;
        }

        public bool Lock(List<string> ids, int _lock)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"update DBSubjectQuestion set Lock=@lk where ID=@TmpId",
                                         new[] { new { lk = _lock, TmpId = id } });
                    if (retCode <= 0)
                    {
                        break;
                    }
                }
                flag = true;
            }
            return flag;
        }

        public bool Update(DBSubjectQuestion entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.DBSubjectQuestion.Update(entity);
            }
        }
        public bool Update(Guid subjectid)
        {
            if (subjectid == null || subjectid == null) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                string sql = @"update DBSubject set Point=(select sum(isnull(Point,0)) from DBQuestion where ID 
                            in(select distinct DBQuestionID from DBSubjectQuestion where 
                            DBSubjectID=@sid)) where ID=@sid";
                int retCode = 0;
                retCode = db.Execute(sql, new[] { new { sid = subjectid } });
                flag = retCode > 0;
            }
            return flag;
        }
        public dynamic Insert(DBSubjectQuestion entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.DBSubjectQuestion.Insert(entity, null);
            }
        }

        public DBSubjectQuestion GetDBSubjectQuestion(Guid questionid, Guid dbSubjectID)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pg.Predicates.Add(Predicates.Field<DBSubjectQuestion>(f => f.DBQuestionID, Operator.Eq, questionid));
                pg.Predicates.Add(Predicates.Field<DBSubjectQuestion>(f => f.DBSubjectID, Operator.Eq, dbSubjectID));

                var result = work.DBSubjectQuestion.GetList(pg);
                return result.ToList().FirstOrDefault();
            }
        }

        public List<DBSubjectQuestion> GetQuestionList(Guid dbSubjectID)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pg.Predicates.Add(Predicates.Field<DBSubjectQuestion>(f => f.DBSubjectID, Operator.Eq, dbSubjectID));
                var result = work.DBSubjectQuestion.GetList(pg);
                return result.ToList();
            }
        }


        public int SetDBSubjectQuestionIndex(Guid qestionid, Guid dbSubjectID, int step)
        {
            if (step == 0) return -1;

            var qustionlist = GetQuestionList(dbSubjectID);
            if (qustionlist != null && qustionlist.Count > 0)
            {
                var psq = GetDBSubjectQuestion(qestionid, dbSubjectID);
                if (psq == null) return 0;

                var curOrderIndex = psq.OrderIndex;
                if ((curOrderIndex + step < 1) || (curOrderIndex + step) > GetMaxOrderIndex(dbSubjectID))
                {
                    return -1;  //移动出了边界
                }

                var tempList = qustionlist.OrderBy(o => o.OrderIndex);

                //向前移动
                if (step < 0)
                {
                    var needmovelist = tempList.Where(o => o.OrderIndex >= (curOrderIndex + step) && o.OrderIndex <= curOrderIndex).ToList().OrderBy(m => m.OrderIndex);
                    foreach (var e in needmovelist)
                    {
                        if (e.OrderIndex == curOrderIndex)
                        {
                            e.OrderIndex = curOrderIndex + step;
                        }
                        else
                        {
                            e.OrderIndex += 1;
                        }

                        Update(e);
                    }



                }
                else  //向后移动
                {
                    var needmovelist = tempList.Where(o => o.OrderIndex <= (curOrderIndex + step) && o.OrderIndex >= curOrderIndex).ToList().OrderBy(m => m.OrderIndex);
                    foreach (var e in needmovelist)
                    {
                        if (e.OrderIndex == curOrderIndex)
                        {
                            e.OrderIndex = curOrderIndex + step;
                        }
                        else
                        {
                            e.OrderIndex -= 1;
                        }

                        Update(e);
                    }


                }
            }

            return 1;
        }

        public int GetMaxOrderIndex(Guid DBSubjectID)
        {

            using (var db = DataContent.DBServer())
            {
                var result = db.Query<int>(string.Format(" select count(*) from DBSubjectQuestion where DBSubjectID='{0}' and status=1", DBSubjectID));
                if (result != null) return result.FirstOrDefault();
                else return 0;
            }

        }

        public int getQuestionOrderIndex(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.PaperSubjectQuestion.GetById(id);
                if (result != null) return result.OrderIndex;
                return 0;
            }
        }

        public List<DBSubjectQuestion> GetListByPage(string sqlwhere, string orderbyFiled, int pagesize, int pageindex, out int totalPage, out int totalRecord)
        {
            if (string.IsNullOrWhiteSpace(orderbyFiled))
                throw new ArgumentNullException("orderbyFiled");

            if (string.IsNullOrWhiteSpace(sqlwhere))
                sqlwhere = " 1=1";
            using (var db = DataContent.DBServer())
            {
                var obj = new
                {
                    TableName = "DBSubjectQuestion",
                    Fields = "*",
                    OrderField = orderbyFiled,
                    sqlWhere = sqlwhere,
                    pageSize = pagesize,
                    pageIndex = pageindex,
                };

                var dynamicParam = new DynamicParameters(obj);
                dynamicParam.Add("TotalPage", null, DbType.Int32, ParameterDirection.Output, 32);
                dynamicParam.Add("TotalRecord", 0, DbType.Int32, ParameterDirection.Output, 32);

                var items = db.Query<DBSubjectQuestion>("PagingRecord", dynamicParam, null, true, null, CommandType.StoredProcedure);
                totalPage = dynamicParam.Get<int>("TotalPage");
                totalRecord = dynamicParam.Get<int>("TotalRecord");
                return items.ToList();
            }
        }

        public Int32 LoadColumns(string filepath, string sheetName, Guid subjectid)
        {
            string strFileName = Path.GetExtension(filepath).ToLower();
            Int32 addcount = 0;

            #region Data

            string strConn;
            if (strFileName == ".xls")
            {

                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath +
                          ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";
            }
            else
            {
                strConn =
                    string.Format(
                        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'",
                        filepath);
            }

            #endregion

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                var ds = new DataSet();
                var oada = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), strConn);
                oada.Fill(ds);
                if (ds.Tables.Count > 0)
                {
                    string strWhere = DateTime.Now.ToString("yyyyMMdd");
                    bool sucess = false;
                    List<string> errorList = new List<string>();

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        if (!Check(row, ref errorList))
                            break;
                        List<List<object>> list = GetDBAnswer(row);
                        if (list != null && list.Count > 0)
                        {
                            int answercount = list.Count;
                            int rightanswercount = GetDBAnswerCount(list);
                            string subjecttype = row.Field<string>("类型");
                            bool addflag = false;

                            if (subjecttype.Equals("多选") && answercount > 2 && rightanswercount > 1)
                                addflag = true;
                            else if (subjecttype.Equals("单选") && answercount > 1 && rightanswercount == 1)
                                addflag = true;
                            if (addflag && !ProxyQuestion.CheckContent(row.Field<string>("题目"), "小题"))
                            {
                                DBQuestion entity = new DBQuestion();
                                entity.ID = Guid.NewGuid();
                                entity.SerialNo = ProxyQuestion.GetSerialNo(strWhere);
                                entity.Content = row.Field<string>("题目");
                                entity.QuestionType = row.Field<string>("类型");
                                entity.SubjectType = subjecttype;
                                entity.DBQuestionType = "小题";
                                entity.KeyWord = row.Field<string>("关键字");
                                entity.Knowledge = row.Field<string>("知识点");
                                entity.Challenge = row.Field<string>("难度");
                                entity.Point = Decimal.Parse(row.Field<double>("分值").ToString());
                                entity.AttachFile = row.Field<string>("附件");
                                entity.Title = "";
                                entity.Stage = row.Field<string>("阶段");
                                entity.Status = 1;
                                entity.AddTime = DateTime.Now;
                                entity.Lock = false;
                                entity.Operator = "admin";
                                entity.OperatorTime = DateTime.Now;

                                var retCode = ProxyQuestion.Insert(entity);
                                sucess = retCode != null;
                                sucess = DBAnswerAdd(sucess, entity.ID, list, subjectid);
                                if (sucess)
                                    addcount++;
                            }
                        }
                    }
                }
            }
            if (addcount > 0)
                Update(subjectid);

            return addcount;
        }

        private bool Check(DataRow row, ref List<string> errorList)
        {
            bool right = true;
            #region 条件

            //if (string.IsNullOrWhiteSpace(row.Field<string>("关键字")))
            //{
            //    errorList.Add("关键字不能为空");
            //    right = false;
            //}
            //if (string.IsNullOrWhiteSpace(row.Field<string>("知识点")))
            //{
            //    errorList.Add("知识点不能为空");
            //    right = false;
            //}
            if (string.IsNullOrEmpty(row.Field<string>("类型")))
            {
                errorList.Add("类型不能为空");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("题型")))
            {
                errorList.Add("题型不能为空");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("难度")))
            {
                errorList.Add("难度不能为空");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("阶段")))
            {
                errorList.Add("阶段不能为空");
                right = false;
            }
            if (!(row.Field<double>("分值") > 0))
            {
                errorList.Add("分值必须大于零");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("题目")))
            {
                errorList.Add("内容题目不能为空");
                right = false;
            }
            if (!string.IsNullOrEmpty(row.Field<string>("题型"))
                && row.Field<string>("题型").Equals("视听") && string.IsNullOrEmpty(row.Field<string>("附件")))
            {
                errorList.Add("视听题，视听附件不能为空");
                right = false;
            }

            #endregion

            return right;
        }
        public List<List<object>> GetDBAnswer(DataRow row)
        {
            List<List<object>> list = new List<List<object>>();
            if (row != null)
            {
                if (row.Field<string>("选项A") != null && row.Field<string>("选项A").Trim().Length > 0)
                {
                    List<object> listA = new List<object>();
                    listA.Add("A");
                    listA.Add(row.Field<string>("选项A"));
                    if (row.Field<string>("选项A正确") != null && row.Field<string>("选项A正确").Trim().Equals("是"))
                        listA.Add("true");
                    else
                        listA.Add("false");
                    list.Add(listA);
                }
                if (row.Field<string>("选项B") != null && row.Field<string>("选项B").Trim().Length > 0)
                {
                    List<object> listB = new List<object>();
                    listB.Add("B");
                    listB.Add(row.Field<string>("选项B"));
                    if (row.Field<string>("选项B正确") != null && row.Field<string>("选项B正确").Trim().Equals("是"))
                        listB.Add(true);
                    else
                        listB.Add(false);
                    list.Add(listB);
                }
                if (row.Field<string>("选项C") != null && row.Field<string>("选项C").Trim().Length > 0)
                {
                    List<object> listC = new List<object>();
                    listC.Add("C");
                    listC.Add(row.Field<string>("选项C"));
                    if (row.Field<string>("选项C正确") != null && row.Field<string>("选项C正确").Trim().Equals("是"))
                        listC.Add("true");
                    else
                        listC.Add("false");
                    list.Add(listC);
                }
                if (row.Field<string>("选项D") != null && row.Field<string>("选项D").Trim().Length > 0)
                {
                    List<object> listD = new List<object>();
                    listD.Add("D");
                    listD.Add(row.Field<string>("选项D"));
                    if (row.Field<string>("选项D正确") != null && row.Field<string>("选项D正确").Trim().Equals("是"))
                        listD.Add("true");
                    else
                        listD.Add("false");
                    list.Add(listD);
                }
                if (row.Field<string>("选项E") != null && row.Field<string>("选项E").Trim().Length > 0)
                {
                    List<object> listE = new List<object>();
                    listE.Add("E");
                    listE.Add(row.Field<string>("选项E"));
                    if (row.Field<string>("选项E正确") != null && row.Field<string>("选项E正确").Trim().Equals("是"))
                        listE.Add("true");
                    else
                        listE.Add("false");
                    list.Add(listE);
                }
            }
            return list;
        }

        public int GetDBAnswerCount(List<List<object>> list)
        {
            int right = 0;
            if (list != null && list.Count > 0)
            {
                foreach (List<object> tlist in list)
                {
                    if ("true".Equals(tlist[2].ToString()))
                        right++;
                }
            }
            return right;
        }


        private bool SubjectQuestionAdd(Guid qid, Guid subjectid)
        {
            bool flag = ChecKSubjectQuestion(subjectid, qid);
            if (!flag)
            {
                DBSubjectQuestion dbsubjectquestion = new DBSubjectQuestion();

                dbsubjectquestion.DBQuestionID = qid;
                dbsubjectquestion.DBSubjectID = subjectid;
                dbsubjectquestion.OrderIndex = GetMaxOrderIndex(subjectid) + 1;
                dbsubjectquestion.ID = Guid.NewGuid();
                dbsubjectquestion.Lock = false;
                dbsubjectquestion.Operator = "admin";
                dbsubjectquestion.OperatorTime = DateTime.Now;
                dbsubjectquestion.Status = 1;

                var retCode = Insert(dbsubjectquestion);
                return retCode != null;
            }
            return false;
        }
        public bool DBAnswerAdd(bool sucess, Guid realId, List<List<object>> list, Guid subjectid)
        {
            if (sucess)
            {
                sucess = SubjectQuestionAdd(realId, subjectid);

                if (sucess)
                {
                    sucess = DBAnswerAdd2(realId, list);

                    if (!sucess)
                    {
                        List<string> listsq = new List<string>();
                        listsq.Add(realId.ToString());
                        ProxyQuestion.Delete(listsq);

                        Delete(subjectid, realId);
                    }
                }
                else
                {
                    List<string> listsq = new List<string>();
                    listsq.Add(realId.ToString());
                    ProxyQuestion.Delete(listsq);
                }
            }
            return sucess;
        }
        public bool DBAnswerAdd2(Guid id, List<List<object>> list)
        {
            DBAnswerService ProxyDBAnswer = ObjectFactory<DBAnswerService>.Create();
            if (list != null && list.Count > 0)
            {
                List<string> listDelete = new List<string>();
                try
                {
                    DBAnswer dbanswer = new DBAnswer();
                    dbanswer.Lock = false;
                    dbanswer.Status = 1;
                    dbanswer.OperatorTime = DateTime.Now;
                    dbanswer.Operator = "admin";
                    dbanswer.DBQuestionID = id;

                    foreach (List<object> templist in list)
                    {
                        dbanswer.ID = Guid.NewGuid();
                        dbanswer.Prefix = templist[0].ToString();
                        dbanswer.Content = templist[1].ToString();
                        dbanswer.IsRight = "true".Equals(templist[2].ToString());
                        ProxyDBAnswer.Insert(dbanswer);
                        listDelete.Add(dbanswer.ID.ToString());
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    ProxyDBAnswer.Delete(listDelete);
                }
            }
            return false;
        }
    }
}
